/**
 * Created by xc on 2017/11/21.
 */

var common = require('../../common');
var pool = require('../index');
var mysql = require('mysql');
var YjxBaseModel = require('./YjxBaseModel');
var async  = require( 'async'); // 同步控制

//--------------------------------------------------------------------------------------
// Schema 结构
var YjxCouponModel = function(obj){
    YjxBaseModel.call(this,obj);

    // 以下为表定义数据，需要的字段就填入
    //`c_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '优惠券id',
    //`lot_no` varchar(20) NOT NULL DEFAULT '0' COMMENT '批次号：年月日时分秒',
    //`org_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '优惠券拥有方: 组织',
    //`owner` bigint(20) NOT NULL DEFAULT '0' COMMENT '优惠券拥有方：组织下的具体成员',
    //`c_type` int(3) NOT NULL DEFAULT '0' COMMENT '类型:10直接抵扣,20满减券,30折扣券',
    //`use` int(3) NOT NULL DEFAULT '10' COMMENT '用途: [保留字段]',
    //`c_code` varchar(20) NOT NULL COMMENT '优惠码',
    //`limit_type` bigint(20) NOT NULL DEFAULT '0' COMMENT '限制范围: 暂定为tg_type分类',
    //`discount` bigint(20) NOT NULL DEFAULT '0' COMMENT '优惠额度:金额单位分(折扣券时为1~99)',
    //`min_order_fee` bigint(20) NOT NULL DEFAULT '0' COMMENT '最小订单金额',
    //`issue_time` datetime NOT NULL COMMENT '发放时间',
    //`expiry_time` datetime DEFAULT NULL COMMENT '有效截止时间',
    //`draw_time` datetime DEFAULT NULL COMMENT '领取/抽取时间(未绑定user)',
    //`exchg_time` datetime DEFAULT NULL COMMENT '兑换时间(绑定到user)',
    //`used_time` datetime DEFAULT NULL COMMENT '使用时间(订单完成)',
    //`mobile` bigint(20) DEFAULT NULL COMMENT '临时用户手机号:未注册用户抽取优惠券时设置,对应优惠券状态为[已占用];该手机号注册后再设置用户id,对应优惠券状态改为[已兑换]',
    //`user_id` bigint(20) DEFAULT NULL COMMENT '用户id',
    //`trade_type` int(3) DEFAULT NULL COMMENT '订单种别: 10商品订单，30购买模版',
    //`trade_obj_id` bigint(20) DEFAULT NULL COMMENT '订单id（商品订单order_id或购买模版buy_id）',
    //`state` int(3) unsigned NOT NULL DEFAULT '10' COMMENT '状态:10[新建],15[已占用/已领取],20[已兑换(绑定到用户)],30[已绑定到订单],40[已使用/完成],50[已过期]',
    //`desc` varchar(200) DEFAULT '' COMMENT '备注',
    //`create_time` datetime NOT NULL,
    //`update_time` datetime NOT NULL,
    //`update_by` varchar(50) DEFAULT NULL,
    //`del_flg` int(1) NOT NULL DEFAULT '0',

};

YjxCouponModel.prototype = new YjxBaseModel();

// 取得表名
YjxCouponModel.prototype.getTableName = function(){
    return 'yjx_coupon';
};
// 取得查询语句中where 后面匹配主键的条件（需要各派生类进行重载）
YjxCouponModel.prototype.getWhereConditionOfKeyEqual = function(){
    return 'c_id='+this.c_id;
};


//--------------------------------------------------------------------------------------
// 释放[取消的(商品)订单]所绑定的优惠券: 仅更新优惠券，不更新订单
YjxCouponModel.prototype.unbindCanceledOrderOnly = function(orderId, callback){
    var THIS = this;

    pool.pool.getConnection(function(err, connection) {

        var sql = " UPDATE yjx.yjx_coupon t, yjx.yjx_trade_order o "+
            " SET t.trade_type=null "+
            "    ,t.trade_obj_id=null "+
            "    ,t.state=20 "+// 绑定到用户
            "    ,t.update_time=sysdate() "+
            " WHERE o.order_id="+orderId+" AND o.coupon_id is not null AND o.state=50 AND o.del_flg=0 "+// 取消的订单
            " AND t.c_id=o.coupon_id AND t.state=30 AND t.del_flg=0 ";// 当前已绑定到订单的优惠券

        console.log(sql);
        connection.query(sql, function (error, result) {
            if (error) {
                console.log('[YjxCouponModel.js] [unbindCanceledOrderOnly] Update Error: ' + error);
            }
            connection.release();

            callback(error, result);
        });
    });
};

//--------------------------------------------------------------------------------------
// 对于已支付成功的订单，更新关联的优惠券状态
YjxCouponModel.prototype.updStateByPaidOrder = function(orderId, callback){
    var THIS = this;

    pool.pool.getConnection(function(err, connection) {

        var sql = " UPDATE yjx.yjx_coupon t, yjx.yjx_trade_order o "+
            " SET t.used_time=sysdate() "+
            "    ,t.state=40 "+// 已使用
            "    ,t.update_time=sysdate() "+
            " WHERE o.order_id="+orderId+" AND o.coupon_id is not null AND o.state in (11,20,30,40) AND o.del_flg=0 "+// 支付成功的订单
            " AND t.c_id=o.coupon_id AND t.state=30 AND t.del_flg=0 ";// 当前已绑定到订单的优惠券

        console.log(sql);
        connection.query(sql, function (error, result) {
            if (error) {
                console.log('[YjxCouponModel.js] [updStateByPaidOrder] Update Error: ' + error);
            }
            connection.release();

            callback(error, result);
        });
    });
};

//--------------------------------------------------------------------------------------
/*
 * 查询推荐码的合法性
 *
 * 参数： couponCode 推荐码
 * 参数： callback   错误信息和结果
 */
YjxCouponModel.prototype.checkCcode = function(couponCode, callback){

    pool.pool.getConnection(function(err, connection) {

        var sql = "SELECT * FROM yjx.yjx_coupon yc WHERE yc.c_code = '"+couponCode+"' AND yc.c_type = 40 AND yc.state = 20 AND  yc.del_flg = 0";

        console.log(sql);
        connection.query(sql, function (error, result) {
            if (error) {
                console.log('[YjxCouponModel.js] [checkCcode]  Error: ' + error);
            }
            connection.release();

            callback(error, result);
        });
    });
};



//获取推荐码列表sql

function sql_getCcodeList(bForCnt,pageNum,pageSize,cType,userId,desc,state) {

    var sql = '';
    if(bForCnt) {
        sql += " SELECT " +
               " COUNT(0) AS `count` ";
    } else  {
        sql += "	SELECT	" +
                "	  yc.c_id,	" +
                "	  yc.c_code,	" +
                "	  yc.c_type,	" +
                "	  yc.state,	" +
                "	  yc.user_id,	" +
                "	  yc.desc,	" +
                " DATE_FORMAT(yc.create_time, '%Y-%m-%d %H:%i:%s') AS create_time,	" +
                " DATE_FORMAT(yc.expiry_time, '%Y-%m-%d %H:%i:%s') AS expiry_time,	" +
                " DATE_FORMAT(yc.update_time, '%Y-%m-%d %H:%i:%s') AS update_time	" ;
    }
    sql+="	FROM	" +
        "	  yjx.yjx_coupon yc	" +
        "	WHERE yc.del_flg = 0	" ;
        if (cType && cType.length > 0) {
            sql += " AND yc.c_type IN ("+cType+") " ;
        }
        if (state && state.length > 0) {
            sql += "  AND yc.state IN ("+state+") " ;
        }
        if (userId && userId>0){
            sql += "  AND yc.user_id = "+userId+"	" ;
        }
        if (desc){
            sql += "  AND yc.desc LIKE '%"+desc+"%'	" ;
        }
        sql +="	ORDER BY yc.create_time DESC	";
    if (!bForCnt) {
        if (typeof pageNum !== 'undefined' && pageNum !== null &&
            typeof pageSize !== 'undefined' && pageSize !== null)
        {
            var skipCnt = Number(pageNum||'0') * Number(pageSize||'0');
            sql += " LIMIT " + skipCnt + "," + Number(pageSize||'0');
        }
    }
    return sql;
}




//--------------------------------------------------------------------------------------
/*
 * 获取推荐码列表
 * 参数：pageNum   请求页码
 * 参数：pageSize   请求页容量
 * 参数: withTotalPage  是否需要总页数
 * 参数: cType    检索条件：码类型（10直接抵扣,20满减券,30折扣券,40活动推广码）
 * 参数：userId   检索条件：用户id（只返回绑定到该用户的码）
 * 参数： desc   检索条件：码备注（模糊匹配）
 * 参数： state  检索条件：码状态
 * 参数： callback   错误信息和结果
 */
YjxCouponModel.prototype.getCcodeList = function(pageNum,pageSize,cType,userId,desc,state, callback){
    pool.pool.getConnection(function(err, connection) {

        var sql = sql_getCcodeList(false,pageNum,pageSize,cType,userId,desc,state);

        console.log(sql);
        connection.query(sql, function (error, result) {
            if (error) {
                console.log('[YjxCouponModel.js] [checkCcode]  Error: ' + error);
            }
            connection.release();

            callback(error, result);
        });
    });
};

//--------------------------------------------------------------------------------------
/*
 * 获取推荐码总个数
 * 参数：pageNum   请求页码
 * 参数：pageSize   请求页容量
 * 参数: withTotalPage  是否需要总页数
 * 参数: cType    检索条件：码类型（10直接抵扣,20满减券,30折扣券,40活动推广码）
 * 参数：userId   检索条件：用户id（只返回绑定到该用户的码）
 * 参数： desc   检索条件：码备注（模糊匹配）
 * 参数： state  检索条件：码状态
 * 参数： callback   错误信息和结果
 */
YjxCouponModel.prototype.getCcodeListCnt = function(pageNum,pageSize,cType,userId,desc,state, callback){
    pool.pool.getConnection(function(err, connection) {

        var sql = sql_getCcodeList(true,pageNum,pageSize,cType,userId,desc,state);
            console.log(sql);
        connection.query(sql, function (error, result) {
            if (error) {
                console.log('[YjxCouponModel.js] [checkCcode]  Error: ' + error);
            }
            connection.release();

            callback(error, result[0].count);
        });
    });
};



/*
 * 获取推广码奖励信息 个数
 * 参数：bForCnt  标记是否查数量
 * 参数：pageNum   请求页码
 * 参数：pageSize   请求页容量
 * 参数: actId    检索条件：活动id
 * 参数：cId   检索条件：推荐码id
 * 参数：cCode   检索条件：推广码
 * 参数：desc  检索条件：推广码描述
 * 参数：userId 检索条件：用户ud
 * 参数： callback   错误信息和结果
 */
function sql_getCcodeRewardList(bForCnt,pageNum,pageSize,actId,cId,userId,desc,cCode,callback){

    var sql = '';
    if(bForCnt) {
        sql += " SELECT " +
            " COUNT(0) AS `count` ";
    } else  {
        sql += "	SELECT	" +
                "	  res.*	" ;
    }
    sql +="	FROM	" +
            "	  (SELECT	" +
            "	    x4.act_id,	" +
            "	    x4.title AS act_name,	" +
            "	    c.c_id,	" +
            "	    c.c_code,	" +
            "	    c.user_id,	" +
            "	    c.desc,	" +
            "	    COUNT(x4.user_id) AS p_total_cnt,	" +
            "	    SUM(x4.valid_user) AS p_valid_cnt,	" +
            "	    SUM(x4.valid_reward) AS p_reward	" +
            "	  FROM	" +
            "	    (SELECT	" +
            "	      x3.*,	" +
            "	      CASE	" +
            "	        WHEN x3.rank <= x3.max	" +
            "	        THEN x3.reward_amt	" +
            "	        ELSE 0	" +
            "	      END AS valid_reward,	" +
            "	      CASE	" +
            "	        WHEN x3.rank <= x3.max	" +
            "	        THEN 1	" +
            "	        ELSE 0	" +
            "	      END AS valid_user	" +
            "	    FROM	" +
            "	      (SELECT	" +
            "	        x2.*,	" +
            "	        CASE	" +
            "	          WHEN @actflg = x2.act_id	" +
            "	          THEN @rank := @rank + 1	" +
            "	          ELSE @rank := 1	" +
            "	        END AS rank 	" +
            "	        ,	" +
            "	        @actflg := x2.act_id	" +
            "	       FROM	" +
            "	        (SELECT	" +
            "	          up.act_id,	" +
            "	          up.aup_id,	" +
            "	          up.user_id,	" +
            "	          up.c_code,	" +
            "	          ya.title,	" +
            "	          ya.as_id,	" +
            "	          rr.arr_id,	" +
            "	          rr.max,	" +
            "	          rr.reward_amt	" +
            "	        FROM	" +
            "	          (SELECT	" +
            "	            aup.act_id,	" +
            "	            aup.user_id,	" +
            "	            MIN(aup.aup_id) AS valid_aup_id	" +
            "	          FROM	" +
            "	            yjx.yjx_act_user_product aup	" +
            "	            INNER JOIN yjx.yjx_coupon yc	" +
            "	              ON yc.c_code = aup.c_code	" +
            "	              AND yc.state = 20	" +
            "	              AND yc.user_id > 0	" +
            "	              AND yc.del_flg = 0 	" +
            "	           WHERE aup.del_flg = 0	" +
            "	            AND aup.state = 30 	" +
            "	            AND aup.org_id = 0 	" +
            "	           GROUP BY aup.act_id,	" +
            "	            aup.user_id) x1	" +
            "	          INNER JOIN yjx.yjx_act_user_product up	" +
            "	            ON up.aup_id = x1.valid_aup_id	" +
            "	            AND up.del_flg = 0	" +
            "	          INNER JOIN yjx.yjx_activity ya	" +
            "	            ON ya.act_id = up.act_id	" +
            "	            AND ya.del_flg = 0	" +
            "	          INNER JOIN yjx.yjx_act_setting yas	" +
            "	            ON yas.as_id = ya.as_id	" +
            "	            AND yas.del_flg = 0	" +
            "	          INNER JOIN yjx.yjx_act_reward_rule rr	" +
            "	            ON rr.as_id = yas.as_id	" +
            "	            AND rr.state = 10	" +
            "	            AND rr.type = 20	" +
            "	            AND rr.del_flg = 0	" +
            "	        ORDER BY up.act_id ASC,	" +
            "	          up.aup_id ASC	" +
            "	        ) x2	" +
            "	        INNER JOIN	" +
            "	          (SELECT	" +
            "	            @rank := 0, @actflg:=0) t	" +
            "	      ORDER BY x2.act_id ASC,	" +
            "	        x2.aup_id ASC	" +
            "	      ) x3) x4	" +
            "	    INNER JOIN yjx.yjx_coupon c	" +
            "	      ON c.c_code = x4.c_code	" +
            "	  GROUP BY x4.act_id,	" +
            "	    x4.c_code	" +
            "	  ORDER BY x4.act_id,	" +
            "	    x4.c_code) res	" +
            "	WHERE  1     	" ;
            if (actId && actId>0){
                sql+= "    AND res.act_id = "+actId;
            }
            if (cId && cId>0){
                sql+= "    AND res.c_id = "+cId;
            }
            if (cCode){
                sql+= "  AND res.c_code like '%"+cCode+"%'";
            }
            if (desc){
                sql+= "  AND res.desc like '%"+desc+"%'";
            }
            if (userId && userId>0){
                sql+= "  AND res.user_id = "+userId;
            }
    if (!bForCnt) {
        if (typeof pageNum !== 'undefined' && pageNum !== null &&
            typeof pageSize !== 'undefined' && pageSize !== null)
        {
            var skipCnt = Number(pageNum||'0') * Number(pageSize||'0');
            sql += " LIMIT " + skipCnt + "," + Number(pageSize||'0');
        }
    }
    return sql;
}




/*
 * 获取推广码奖励信息 个数
 * 参数：pageNum   请求页码
 * 参数：pageSize   请求页容量
 * 参数: actId    检索条件：活动id
 * 参数：cId   检索条件：推荐码id
 * 参数：cCode   检索条件：推广码
 * 参数：desc  检索条件：推广码描述
 * 参数：userId 检索条件：用户ud
 * 参数：cCode  检索条件：推荐码
 * 参数： callback   错误信息和结果
 */
YjxCouponModel.prototype.getCcodeRewardListCnt = function(pageNum,pageSize,actId,cId,userId,desc, cCode,callback){
    pool.pool.getConnection(function(err, connection) {

        var sql = sql_getCcodeRewardList(true,pageNum,pageSize,actId,cId,userId,desc, cCode);
        console.log(sql);
        connection.query(sql, function (error, result) {
            if (error) {
                console.log('[YjxCouponModel.js] [getCcodeRewardListCnt]  Error: ' + error);
            }
            connection.release();

            callback(error, result[0].count);
        });
    });
};


/*
 * 获取推广码奖励信息 列表
 * 参数：pageNum   请求页码
 * 参数：pageSize   请求页容量
 * 参数: actId    检索条件：活动id
 * 参数：cId   检索条件：推荐码id
 * 参数：cCode   检索条件：推广码
 * 参数：desc  检索条件：推广码描述
 * 参数：userId 检索条件：用户ud
 * 参数：cCode  检索条件：推荐码
 * 参数： callback   错误信息和结果
 */
YjxCouponModel.prototype.getCcodeRewardList = function(pageNum,pageSize,actId,cId,userId,desc,cCode,callback){
    pool.pool.getConnection(function(err, connection) {

        var sql = sql_getCcodeRewardList(false,pageNum,pageSize,actId,cId,userId,desc, cCode);
        console.log(sql);
        connection.query(sql, function (error, result) {
            if (error) {
                console.log('[YjxCouponModel.js] [getCcodeRewardList]  Error: ' + error);
            }
            connection.release();

            callback(error, result);
        });
    });
};

//--------------------------------------------------------------------------------------
// 导出
module.exports = YjxCouponModel;